﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class Reports_DataReporte2_2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String Ruta = Request.QueryString["ruta"];
        String[] fechas = GetfechasByRuta(Ruta);
        for (int x = 0; x < fechas.Length; x++)
        {
            ListItem oItem = new ListItem(fechas[x], fechas[x]);
            // Lo agrega a la colección de Items del DropDownList
            DropDownList1.Items.Add(oItem);
        }



    }
    protected void DropDownList1_Load(object sender, EventArgs e)
    {
       
    }
    public String[] GetfechasByRuta(String Ruta)
    {
        SqlConnection conexion = new SqlConnection("Data Source=engazado.com;Initial Catalog=EnGasAdo;Persist Security Info=True;User ID=System;Password=55552735;");
        String[] Salida;
        try
        {

            conexion.Open();



            string sql = "SELECT DISTINCT dbo.Precio.fecha " +
"FROM            dbo.tbl_ruta INNER JOIN "+
"                         dbo.tbl_gasolinera_x_ruta ON dbo.tbl_ruta.id_ruta = dbo.tbl_gasolinera_x_ruta.id_ruta INNER JOIN "+
"                         dbo.Gasolinera ON dbo.tbl_gasolinera_x_ruta.id_gasolinera = dbo.Gasolinera.ID_Gasolinera INNER JOIN "+
"                         dbo.Precio ON dbo.Gasolinera.ID_Gasolinera = dbo.Precio.ID_Gasolinera "+
"WHERE        (dbo.tbl_ruta.nombre_ruta = '"+Ruta+"')";
                 /*"SELECT DISTINCT dbo.Precio.fecha " +
                    "FROM            dbo.tbl_ruta INNER JOIN " +
                    "                         dbo.tbl_gasolinera_x_ruta ON dbo.tbl_ruta.id_ruta = dbo.tbl_gasolinera_x_ruta.id_ruta INNER JOIN " +
                    "                         dbo.Gasolinera ON dbo.tbl_gasolinera_x_ruta.id_gasolinera = dbo.Gasolinera.ID_Gasolinera INNER JOIN " +
                    "                         dbo.Precio ON dbo.Gasolinera.ID_Gasolinera = dbo.Precio.ID_Gasolinera " +
                    "WHERE        (dbo.tbl_ruta.nombre_ruta = '" + Ruta + "')";*/

            SqlCommand myCommand = new SqlCommand(sql, conexion);
            SqlDataReader resultadoSQL = myCommand.ExecuteReader();
            int leng = GetCountFecha(Ruta);
            Salida= new String[leng];
            int count = 0;
            while (resultadoSQL.Read()){
            
                
                Salida[count] = resultadoSQL.GetDateTime(0).ToString();
                
                //precios
               
                count++;
            }


            return Salida;
        }
        catch (Exception e)
        {
            return null;
        }
        finally
        {
            conexion.Close();
        }

    }


    public int GetCountFecha(String Ruta)
    {
        SqlConnection conexion = new SqlConnection("Data Source=engazado.com;Initial Catalog=EnGasAdo;Persist Security Info=True;User ID=System;Password=55552735;");
        String[] Salida;
        try
        {

            conexion.Open();



            string sql = "SELECT DISTINCT dbo.Precio.fecha " +
"FROM            dbo.tbl_ruta INNER JOIN " +
"                         dbo.tbl_gasolinera_x_ruta ON dbo.tbl_ruta.id_ruta = dbo.tbl_gasolinera_x_ruta.id_ruta INNER JOIN " +
"                         dbo.Gasolinera ON dbo.tbl_gasolinera_x_ruta.id_gasolinera = dbo.Gasolinera.ID_Gasolinera INNER JOIN " +
"                         dbo.Precio ON dbo.Gasolinera.ID_Gasolinera = dbo.Precio.ID_Gasolinera " +
"WHERE        (dbo.tbl_ruta.nombre_ruta = '" + Ruta + "')";

            SqlCommand myCommand = new SqlCommand(sql, conexion);
            SqlDataReader resultadoSQL = myCommand.ExecuteReader();

            int count = 0;
            while (resultadoSQL.Read())
            {

               

                count++;
            }


            return count;
        }
        catch (Exception e)
        {
            return 0;
        }
        finally
        {
            conexion.Close();
        }

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        String fecha = "";
        String Ruta = Request.QueryString["ruta"];
        string date = DropDownList1.SelectedItem.Text;
        DateTime dt = Convert.ToDateTime(date);
        fecha = String.Format("{0:yyyy-MM-dd}", dt);
        Response.Redirect("Reporte2.aspx?ruta=" + Ruta+ "&Fecha=" + fecha);
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}